import os
import time

import pymysql
from PySide6.QtWidgets import QMessageBox
from pymysql import OperationalError, Connection, converters
from pymysql.cursors import DictCursor


class Model:
    DB: Connection = None
    CURSOR: DictCursor = None

    def __init__(self, host: str, user: str, password: str, port: int):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        Model.connect(host=self.host, user=self.user,
                      password=self.password,
                      port=self.port)
        self.affected_rows = 0
        self.selected_db = None
        self.__app__ = None

    def set_app(self, app):
        self.__app__ = app

    @staticmethod
    def connect(host: str, user: str, password: str, port: int) -> DictCursor:
        if Model.DB is None:
            Model.DB: Connection = pymysql.connect(
                host=host,
                user=user,
                password=password,
                port=port,
                autocommit=True,
                cursorclass=DictCursor,
            )
            Model.CURSOR: DictCursor = Model.DB.cursor()
        return Model.CURSOR

    @staticmethod
    def close():
        if Model.CURSOR is not None:
            Model.CURSOR.close()
            Model.CURSOR = None
        if Model.DB is not None:
            Model.DB.close()
            Model.DB = None

    def cursor_execute(self, sql: str):
        cursor = Model.CURSOR
        print(time.strftime("%Y-%m-%d %H:%M:%S"), sql)
        from util.history_file import write as history_file_write
        history_file_write(sql)

        if Model.CURSOR is None:
            result = QMessageBox.question(self.__app__, '连接错误', f'是否重新连接？')
            if result != QMessageBox.StandardButton.Yes:
                return
            Model.DB.connect()
            cursor = Model.CURSOR = Model.DB.cursor()
            if self.selected_db is not None:
                Model.DB.select_db(self.selected_db)

        try:
            self.affected_rows = cursor.execute(sql)
        except OperationalError as e:
            # 数据库断开重连
            # pymysql.constants.CR_SERVER_LOST:
            # pymysql.constants.CR_SERVER_GONE_ERROR:
            if e.args[0] in [2013, 2006]:
                code, msg = e.args
                result = QMessageBox.question(self.__app__, '连接错误', f'{msg}，是否重新连接？')
                Model.CURSOR.close()
                Model.CURSOR = None
                if result != QMessageBox.StandardButton.Yes:
                    return
                Model.DB.connect()
                cursor = Model.CURSOR = Model.DB.cursor()
                if self.selected_db is not None:
                    Model.DB.select_db(self.selected_db)
                self.affected_rows = cursor.execute(sql)
            else:
                raise e

        return cursor

    def show_databases(self):
        cursor = self.cursor_execute('SHOW DATABASES')
        return [i['Database'] for i in cursor.fetchall()]

    def select_db(self, db: str):
        Model.DB.select_db(db=db)
        self.selected_db = db

    def show_engines(self):
        cursor = self.cursor_execute('SHOW ENGINES')
        data = cursor.fetchall()
        return data

    def show_tables(self):
        cursor = self.cursor_execute('SHOW TABLES')
        data = cursor.fetchall()
        return [list(i.values())[0] for i in data]

    def create_table(self, table_name: str, table_encoding: str, table_collection: str, table_engine: str):
        sql = f"CREATE TABLE `{table_name}` (" \
              f"`id` int(11) NOT NULL AUTO_INCREMENT," \
              f"PRIMARY KEY (`id`)" \
              f") ENGINE = {table_engine} AUTO_INCREMENT = 1 " \
              f"DEFAULT CHARSET = {table_encoding} COLLATE = {table_collection}"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def show_create_table(self, table_name: str):
        cursor = self.cursor_execute(f'SHOW CREATE TABLE `{table_name}`')
        return cursor.fetchone()

    def current_database(self):
        return self.selected_db

    def table_index(self, database: str, table_name: str):
        sql = f"SHOW INDEX FROM `{database}`.`{table_name}`"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def table_columns(self, database: str, table_name: str = ''):
        sql = f'SELECT * FROM information_schema.`COLUMNS` ' \
              f'WHERE `TABLE_SCHEMA` = "{database}"'
        if len(table_name) != 0:
            sql += f' AND `TABLE_NAME` = "{table_name}"'
        sql += f" ORDER BY `ORDINAL_POSITION` ASC"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def table_information(self, database: str, table_name: str):
        sql = f"SELECT * FROM `information_schema`.`TABLES`" \
              f" WHERE `TABLE_SCHEMA` = '{database}' AND `TABLE_NAME` = '{table_name}'"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def show_columns_from_table(self, table_name: str):
        sql = f"SHOW COLUMNS FROM `{table_name}`"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def table_content(self,
                      table_name: str,
                      where: list = [],
                      order_by: dict = { },
                      offset: int = 0,
                      limit: int = 2000):
        condition = self.__where__(where)
        sql_order_by = self.__order_by__(order_by)
        sql = f'SELECT * FROM `{table_name}`{condition} {sql_order_by} LIMIT {offset}, {limit}'
        cursor = self.cursor_execute(sql)
        data = cursor.fetchall()
        count = self.table_count(table_name=table_name, where=where)
        return count, data

    def __order_by__(self, order_by: dict):
        sql_list = []
        for field, desc in order_by.items():
            sql_list.append(f'{field} {desc}')

        if len(sql_list) == 0:
            return ''
        else:
            return 'ORDER BY ' + ', '.join(sql_list)

    def __where__(self, conditions):
        where = []
        """ex"""
        conditions_1 = { "role": "xxx" }
        conditions_1 = { "role": 1 }
        conditions_1 = { "role": [1, 2, 3] }
        """ex"""
        if isinstance(conditions, dict):
            for field, value in conditions.items():
                if isinstance(value, str):
                    value = pymysql.escape_string(value)
                    where.append(f"{field}='{value}'")
                elif isinstance(value, int):
                    where.append(f"{field}={value}")
                elif isinstance(value, list):
                    value = ', '.join([str(x) for x in value])
                    where.append(f"{field} in ({value})")
        elif isinstance(conditions, list):
            for condition in conditions:
                if isinstance(condition, dict):
                    for field, value in conditions.items():
                        if isinstance(value, str):
                            value = pymysql.escape_string(value)
                            where.append(f"{field}='{value}'")
                        elif isinstance(value, int):
                            where.append(f"{field}={value}")
                        elif isinstance(value, list):
                            value = ', '.join([str(x) for x in value])
                            where.append(f"{field} in ({value})")
                elif isinstance(condition, list):
                    if len(condition) == 3:
                        field = condition[0]
                        op = condition[1]
                        value = f'"{condition[2]}"'
                        where.append(f"`{field}` {op} {value}")

        if len(where) == 0:
            return ''
        return " WHERE " + ' AND '.join(where)

    def record_insert(self, table_name: str, value: dict):
        field_list = list(value.keys())
        field_value = [converters.escape_string(i) for i in list(value.values())]
        sql = f'INSERT INTO `{table_name}` (`' + \
              "`, `".join(field_list) + '`) VALUES ("' \
              + '", "'.join(field_value) + '");'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def record_update(self, table_name: str, where: dict, value: dict):
        sql = f'UPDATE {table_name} SET '
        for k, v in value.items():
            if isinstance(v, str):
                v = converters.escape_string(v)
                sql += f"{k} = '{v}',"
            elif isinstance(v, int):
                sql += f'{k} = {v},'

        sql = sql[:-1] + ' WHERE '
        for k, v in where.items():
            if isinstance(v, str):
                sql += f'{k} = "{v}"'
            elif isinstance(v, int):
                sql += f'{k} = {v}'

        sql += ' LIMIT 1'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def delete_record(self, table_name: str, where: dict):
        sql = f'DELETE FROM {table_name} WHERE '
        for k, v in where.items():
            if isinstance(v, str):
                sql += f'{k} = "{v}"'
            elif isinstance(v, int):
                sql += f'{k} = {v}'

        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_count(self, table_name: str, where: dict = { }):
        condition = self.__where__(where)
        sql = f'SELECT count(1) AS _count FROM `{table_name}`{condition}'
        cursor = self.cursor_execute(sql)
        result = cursor.fetchone()
        return result['_count']

    def collation_character_set_applicability(self):
        sql = f'SELECT * FROM `information_schema`.`COLLATION_CHARACTER_SET_APPLICABILITY`'
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def table_rename(self, src: str, dst: str):
        sql = f"RENAME TABLE `{src}` TO `{dst}`"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_delete(self, table_name: str):
        sql = f'DROP TABLE {table_name}'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_add_index(self, table_name: str, index_type: str, index_name: str, columns: list):
        column_text = "`" + "`,`".join(columns) + "`"
        if index_type.count("PRIMARY") > 0:
            sql = f'ALTER TABLE `{table_name}` ADD PRIMARY KEY ({column_text})'
        else:
            sql = f'ALTER TABLE `{table_name}` ADD {index_type} {index_name} ({column_text})'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_drop_index(self, table_name: str, index_name: str):
        if index_name.count("PRIMARY") > 0:
            sql = f'ALTER TABLE `{table_name}` DROP PRIMARY KEY'
        else:
            sql = f'ALTER TABLE `{table_name}` DROP INDEX {index_name}'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_drop_column(self, table_name: str, field: str):
        sql = f'ALTER TABLE {table_name} DROP COLUMN {field}'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def reset_auto_increment(self, table_name: str):
        sql = f'ALTER TABLE {table_name} AUTO_INCREMENT = 1'
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def table_truncate(self, table_name: str):
        sql = f'TRUNCATE TABLE {table_name} '
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def setup_transaction(self):
        sql = "SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def start_transaction(self):
        sql = "START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def commit_transaction(self):
        sql = "COMMIT"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def show_views(self, database: str):
        if len(database) == 0:
            database = self.current_database()
        sql = "SELECT TABLE_NAME AS tbl_name " + \
              "FROM INFORMATION_SCHEMA.TABLES " + \
              f"WHERE TABLE_TYPE='VIEW' AND TABLE_SCHEMA='{database}' " + \
              "ORDER BY TABLE_NAME"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def show_triggers(self, database: str):
        if len(database) == 0:
            database = self.current_database()
        sql = f"SHOW TRIGGERS FROM `{database}`;"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def show_procedure(self, database: str):
        if len(database) == 0:
            database = self.current_database()
        sql = "SELECT SPECIFIC_NAME AS procedure_name " + \
              "FROM INFORMATION_SCHEMA.ROUTINES " + \
              f"WHERE ROUTINE_TYPE='PROCEDURE' AND ROUTINE_SCHEMA='{database}'"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def show_function(self, database: str):
        if len(database) == 0:
            database = self.current_database()
        sql = "SELECT SPECIFIC_NAME def_name " + \
              "FROM INFORMATION_SCHEMA.ROUTINES " + \
              f"WHERE ROUTINE_TYPE='FUNCTION' AND ROUTINE_SCHEMA='{database}'"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def show_events(self, database: str):
        if len(database) == 0:
            database = self.current_database()
        sql = f"SELECT EVENT_NAME AS event_name FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA='{database}'"
        cursor = self.cursor_execute(sql)
        return cursor.fetchall()

    def lock_table(self, table_name: str):
        sql = f"LOCK TABLES `{table_name}` READ LOCAL"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def unlock_table(self):
        sql = f"UNLOCK TABLES"
        cursor = self.cursor_execute(sql)
        return cursor.fetchone()

    def start_add_lock_table(self, table_name: str):
        return f"LOCK TABLES `{table_name}` WRITE;"

    def end_add_lock_table(self):
        return "UNLOCK TABLES;" + os.linesep

    def start_add_disable_keys(self, table_name: str):
        return f"/*!40000 ALTER TABLE `{table_name}` DISABLE KEYS */;" + os.linesep

    def end_add_disable_keys(self, *args):
        return "/*!40000 ALTER TABLE `{args[0]}` ENABLE KEYS */;" + os.linesep
